package com.avril.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Vector;

import com.avril.constants.Constants;
import com.avril.constants.DBConstants;
import com.avril.entity.Goods;
import com.avril.entity.GoodsType;
import com.avril.entity.Order;
import com.avril.entity.OrderDetail;
import com.avril.util.ErrorManager;

public class OrderDao implements DBConstants {
	SqlManager manage = null;

	public OrderDao() {
		super();
		manage = SqlManager.createInstance();
		manage.connectDB();
	}

	public Vector<Order> getAllOutOrders(String condition,String inOrOut) {
		String sql = "select * from orderinfo o where o.inorout = '"+inOrOut+"'"
				+ condition;
		ResultSet rs = manage.executeQuery(sql, null, PSTM_TYPE);
		Vector<Order> orders = new Vector<Order>();
		try {
			while (rs.next()) {
				Order order = new Order();
				Vector<OrderDetail> details = new Vector<OrderDetail>();
				order.setOrderCode(rs.getString(ORDER_CODE));
				order.setOrderId(rs.getInt(ORDER_ID));
				order.setCreateWhen(rs.getDate(CREATE_WHEN));
				order.setOrderStatus(rs.getString(ORDER_STATUS));
				order.setTotalPrice(rs.getFloat(TOTAL_PRICE));
				order.setUserName(rs.getString(USER_NAME));
				order.setInOrOut("1");
				String tmpSQL = "select d.*,g.*,t.typename from detail d,goods g,type t where d.orderid = ? and d.goodscode = g.goodscode and t.typeid = g.typeid";
				Object[] params = new Object[] { rs.getInt(ORDER_ID) };
				ResultSet resultSet = manage.executeQuery(tmpSQL, params,
						PSTM_TYPE);
				while (resultSet.next()) {
					OrderDetail detail = new OrderDetail();
					detail.setDetailId(resultSet.getInt(DETAIL_ID));
					detail.setAmount(resultSet.getInt(DETAIL_AMOUNT));
					detail.setTotalPrice(resultSet.getFloat(TOTAL_PRICE));
					detail.setDisPrice(resultSet.getFloat(DIS_PRICE));
					Goods goods = new Goods();
					goods.setAmount(resultSet.getInt(AMOUNT));
					goods.setGoodsCode(resultSet.getString(GOODS_CODE));
					goods.setGoodsId(resultSet.getInt(GOODS_ID));
					goods.setGoodsName(resultSet.getString(GOODS_NAME));
					goods.setInPrice(resultSet.getFloat(IN_PRICE));
					goods.setLocation(resultSet.getString(LOCATION));
					goods.setOutPrice(resultSet.getFloat(OUT_PRICE));
					GoodsType goodsType = new GoodsType(resultSet
							.getInt(TYPE_ID), resultSet.getString(TYPE_NAME));
					goods.setGoodsType(goodsType);
					detail.setGoods(goods);
					details.add(detail);
				}
				order.setOrderDetails(details);
				orders.add(order);
			}
		} catch (SQLException e) {
			ErrorManager.printError("OrderDao.getAllOrderCount", e);
		}
		return orders;
	}

	public int getAllOrderCount() {
		int count = 0;
		String sql = "select count(*) from orderinfo";
		ResultSet rs = manage.executeQuery(sql, null, DBConstants.PSTM_TYPE);
		try {
			rs.next();
			count = Integer.valueOf(rs.getString("count(*)"));
		} catch (SQLException e) {
			ErrorManager.printError("OrderDao.getAllOrderCount", e);
		}
		return count;
	}

	public int getAllOrderCountByType(String type) {
		int count = 0;
		String sql = "select count(*) from orderinfo where inorout = ?";
		Object[] params = new Object[] { type };
		ResultSet rs = manage.executeQuery(sql, params, DBConstants.PSTM_TYPE);
		try {
			rs.next();
			count = Integer.valueOf(rs.getString("count(*)"));
		} catch (SQLException e) {
			ErrorManager.printError("OrderDao.getAllOrderCountByType", e);
		}
		return count;
	}

	public int getOrderIdByOrderCode(String orderCode) {
		int id = 0;
		String sql = "select orderid from orderinfo where ordercode = ?";
		Object[] params = new Object[] { orderCode };
		ResultSet rs = manage.executeQuery(sql, params, DBConstants.PSTM_TYPE);
		try {
			rs.next();
			id = Integer.valueOf(rs.getString("orderid"));
		} catch (SQLException e) {
			ErrorManager.printError("OrderDao.getOrderIdByOrderCode", e);
		}
		return id;
	}

	public boolean addOrder(Order order) {
		String orderCode = order.getOrderCode();
		ArrayList<String> revertLogs = new ArrayList<String>();
		boolean result = false;
		try {
			Object[] params = new Object[] { orderCode, order.getUserName(),
					order.getTotalPrice(), new Date(), order.getOrderStatus(),
					order.getInOrOut() };
			String sql = "insert into orderinfo (ordercode,username,totalprice,createwhen,orderstatus,inorout) values(?,?,?,?,?,?)";
			result = manage.executeUpdate(sql, params, DBConstants.PSTM_TYPE);
			if (result) {
				int orderId = getOrderIdByOrderCode(orderCode);
				for (OrderDetail orderDetail : order.getOrderDetails()) {
					String goodsCode = orderDetail.getGoods().getGoodsCode();
					if (order.getInOrOut().equals(Constants.INT_IN_VALUE)) {
						GoodsDao goodsDao = new GoodsDao();
						result = goodsDao.addGoods(orderDetail.getGoods());
						revertLogs.add("delete from goods where goodscode = "
								+ goodsCode);
					}
					manage.connectDB();
					String detailSql = "insert into detail (goodscode,disprice,detailamount,totalprice,orderid,inorout) values (?,?,?,?,?,?)";
					Object[] detailParams = new Object[] { goodsCode,
							orderDetail.getDisPrice(), orderDetail.getAmount(),
							orderDetail.getTotalPrice(), orderId,
							orderDetail.getInOrOut() };
					result = manage.executeUpdate(detailSql, detailParams,
							DBConstants.PSTM_TYPE);
					if (result) {
						if (order.getInOrOut().equals(Constants.INT_OUT_VALUE)) {
							result = manage
									.executeUpdate(
											"update goods set amount = ? where goodscode = ?",
											new Object[] {
													orderDetail.getGoods()
															.getAmount()
															- orderDetail
																	.getAmount(),
													goodsCode },
											DBConstants.PSTM_TYPE);
							revertLogs.add("update goods set amount = "
									+ orderDetail.getGoods().getAmount()
									+ " where goodscode =" + goodsCode);
						}
					}
				}

			}
			manage.closeDB();
		} catch (Exception e) {
			deleteOrder("ordercode", orderCode);
			for (String log : revertLogs) {
				manage.executeUpdate(log, null, DBConstants.PSTM_TYPE);
			}
			ErrorManager.printError("OrderDao.addOrder", e);
		}
		return result;
	}

	public boolean deleteOrder(String key, String value) {
		boolean result = false;
		try {
			String sql = "delete from orderinfo where " + key + " = ?";
			Object[] params = new Object[] { value };

			result = manage.executeUpdate(sql, params, DBConstants.PSTM_TYPE);
			manage.closeDB();
		} catch (Exception e) {
			ErrorManager.printError("OrderDao.addOrder", e);
		}
		return result;
	}

	public boolean cancelOrder(Order order) {
		boolean result = false;
		try {
			String sql = "update orderinfo set orderstatus = ? where orderid = ?";
			Object[] params = new Object[] { order.getOrderStatus(),
					order.getOrderId() };
			result = manage.executeUpdate(sql, params, DBConstants.PSTM_TYPE);
			if (result) {
				for (OrderDetail detail : order.getOrderDetails()) {
					Goods goods = detail.getGoods();
					String tmpSQL = "update goods set amount = ( amount + ?) where goodscode = ?";
					Object[] tmpParams = new Object[] { detail.getAmount(),
							goods.getGoodsCode() };
					result = manage.executeUpdate(tmpSQL, tmpParams, PSTM_TYPE);
				}
			}
			manage.closeDB();
		} catch (Exception e) {
			ErrorManager.printError("OrderDao.cancelOrder", e);
		}
		return result;
	}

	public static void main(String[] args) {
		OrderDao orderDao = new OrderDao();
		// Order order = new Order();
		// order.setOrderCode("123");
		// order.setUserName("sedsd");
		// order.setTotalPrice(100l);
		// order.setOrderStatus(Constants.STATUS_OF_FINISH);
		// order.setInOrOut(Constants.INT_OUT_VALUE);
		// System.out.println(orderDao.addOrder(order));
		orderDao.deleteOrder("ordercode", "20110422160737");
	}
}
